package com.foreknow.elm.dao.impl;

import com.foreknow.elm.dao.DeliveryAddressDao;
import com.foreknow.elm.po.Deliveryaddress;
import com.foreknow.elm.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DeliveryAddressDaoImpl  implements DeliveryAddressDao {
    private Connection con;
    private PreparedStatement pst;
    private ResultSet rs;
    @Override
    public List<Deliveryaddress> listDeliveryAddressByUserId(String userId)  throws Exception {
        String sql = "SELECT * FROM deliveryaddress WHERE userId=? ORDER BY userId";
        List<Deliveryaddress> list = new ArrayList<>();
        try {
            //Connection从ThreadLocal中获取
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            //将？替换具体的值
            pst.setString(1,userId);
            //执行查询
            rs = pst.executeQuery();
            while (rs.next()){
                Deliveryaddress address = new Deliveryaddress();
                address.setDaId(rs.getInt("daId"));
                address.setContactName(rs.getString("contactName"));
                address.setContactSex(rs.getInt("contactSex"));
                address.setContactTel(rs.getString("contactTel"));
                address.setAddress(rs.getString("address"));
                address.setUserId(rs.getString("userId"));
                //将对象添加到list中
                list.add(address);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Deliveryaddress> getDeliveryAddressById(Integer daId)  throws Exception {
        String sql = "SELECT * FROM deliveryaddress WHERE daId=? ";
        List<Deliveryaddress> list = new ArrayList<>();
        try {
            //Connection从ThreadLocal中获取
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            //将？替换具体的值
            pst.setInt(1,daId);
            //执行查询
            rs = pst.executeQuery();
            while (rs.next()) {
                Deliveryaddress address = new Deliveryaddress();
                address.setDaId(rs.getInt("daId"));
                address.setContactName(rs.getString("contactName"));
                address.setContactSex(rs.getInt("contactSex"));
                address.setContactTel(rs.getString("contactTel"));
                address.setAddress(rs.getString("address"));
                address.setUserId(rs.getString("userId"));
                //将对象添加到list中
                list.add(address);
            }
            } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int saveDeliveryAddress(String contactName, Integer contactSex, String contactTel, String address, String userId)  throws Exception{
        String sql = "insert into DeliveryAddress (contactName,contactSex,contactTel,address,userId) value(?,?,?,?,?)";
        int num = 0;
        try {
            //Connection从ThreadLocal中获取
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            //将？替换具体的值
            pst.setString(1,contactName);
            pst.setInt(2,contactSex);
            pst.setString(3,contactTel);
            pst.setString(4,address);
            pst.setString(5,userId);
            //执行查询
            num = pst.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int updateDeliveryAddress(Integer daId, String contactName, Integer contactSex, String contactTel, String address, String userId)  throws Exception{
        String sql = "update deliveryaddress set contactName=?,contactSex=?,contactTel=?,address=?,userId=? where daId=? ";
        int update=0;
        try {
            //Connection从ThreadLocal中获取
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            //将？替换具体的值
            pst.setString(1,contactName);
            pst.setInt(2,contactSex);
            pst.setString(3,contactTel);
            pst.setString(4,address);
            pst.setString(5,userId);
            pst.setInt(6,daId);
            //执行查询
            update = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,pst);
        }

        return update;
    }

    @Override
    public int removeDeliveryAddress(Integer daId) {
        String sql ="delete from deliveryaddress where daId = ?";
        int delete=0;
        try {
            //Connection从ThreadLocal中获取
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            //将？替换具体的值
            pst.setInt(1,daId);
            //执行查询
            delete = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return delete;
    }

}
